/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package data;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.jdom.Element;
import org.jdom.Namespace;

/**
 *
 * @author stana
 */
public class GPSRecordTable
{

    private java.sql.Connection conn;
    private Statement statement;

    public GPSRecordTable()
    {
        conn = data.Connection.getInstance().getConnection();
        statement = data.Connection.getInstance().getStatement();
    }

    public void insertData(double lat, double lng, double alt,double speed, String time, int trainingId)
    {
        int id = getLastIndex();
        id++;
        try
        {
            statement.execute("insert into gps_record values(" + String.valueOf(id) + ","
                    + String.valueOf(lat)
                    + "," + String.valueOf(lng)
                    + "," + String.valueOf(alt)
                    + ",'" + time
                    + "'," + String.valueOf(trainingId)
                    +  ","+String.valueOf(speed)
                    +");");
        } catch (SQLException ex)
        {
            Logger.getLogger(TrainingTable.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void deleteData(int trainingId)
    {
        try
        {
            statement.executeUpdate("delete from gps_record where training_id="+trainingId);
        } catch (SQLException ex)
        {
            Logger.getLogger(GPSRecordTable.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void addBatchData(int id, double lat, double lng, double alt, String time, int trainingId)
    {
        try
        {
            statement.addBatch("insert into gps_record values(" + String.valueOf(id) + ","
                    + String.valueOf(lat)
                    + "," + String.valueOf(lng)
                    + "," + String.valueOf(alt)
                    + ",'" + time
                    + "'," + String.valueOf(trainingId)
                    + ");");
        } catch (SQLException ex)
        {
            Logger.getLogger(TrainingTable.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void insertList(List<Element> list, int trainingId)
    {

        String insert = "";
        String lat="0", lng="0", alt="0",speed="0";
        int id;
        String time="2000-2-22T0:0:0Z";
        Namespace namespace = Namespace.getNamespace("http://www.topografix.com/GPX/1/0");
        id = getLastIndex();
        try
        {
            conn.setAutoCommit(false);
            for (Element e : list)
            {
                id++;
                try{
                lat = e.getAttribute("lat").getValue();
                lng = e.getAttribute("lon").getValue();
                Element ele = e.getChild("ele", namespace);
                alt = ele.getValue();
                time = e.getChild("time", namespace).getValue();
                speed=e.getChild("speed",namespace).getValue();
                }
                catch(NullPointerException ex)
                {
                    
                }
                insert = "insert into gps_record values(" + String.valueOf(id) + ","
                        + lat + "," + lng + "," + alt
                        + ",'" + time
                        + "'," + String.valueOf(trainingId)+","+speed
                        + ");";
                statement.executeUpdate(insert);
            }          
            conn.commit();
            conn.setAutoCommit(true);
        } catch (SQLException ex)
        {
            Logger.getLogger(GPSRecordTable.class.getName()).log(Level.SEVERE, null, ex);
        }
       
    }

    public void batchStatement()
    {
        try
        {
            statement.executeBatch();
        } catch (SQLException ex)
        {
            Logger.getLogger(GPSRecordTable.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public int getLastIndex()
    {
        try
        {
            ResultSet rs = statement.executeQuery("select max(id) from gps_record;");
            while (rs.next())
            {
                return rs.getInt("max(id)");
            }

        } catch (SQLException ex)
        {
            Logger.getLogger(TrainingTable.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }
}
